-- **************************************************************************
--    Project Name:   三段码概要表
--    Job Name:       jms_dim.dim_first_second_third_code_info
--    Description :   将一二三段数据合并在一张维表中,使用方便
--    Author :        郭瑞玲
--    date：          2022/05/22
-- **************************************************************************
--    Relation ：
--    jms_dim.dim_first_second_third_code_info << [
--        jms_ods.yl_lmdm_sys_first_code,
--        jms_ods.yl_lmdm_sys_second_code,
--        jms_ods.yl_lmdm_sys_third_code,
--        jms_ods.yl_lmdm_sys_area
--    ]
--    update_time : 2023-02-13 添加目的中心 dest_center_code dest_center_name
-- **************************************************************************
---一段码
/*  type字段
    TRANSPORT(1, "转运包"),
    IN_CITY(2, "市内包"),
    DISTRIBUTION(3, "集散包"),
    DIRECT(4, "直达包"),
*/
/*  attributes字段
    SPLIT(1, "拆"),
    DIRECTLY(2, "直"),
*/
with first_code as (
    select *
    from (
        select
            code
             ,name
             ,case when attributes=1 then '拆'
                   when attributes=2 then '直'
                   else attributes
            end as attributes
             ,center_id
             ,center_name
             ,center_code
             ,dest_center_code --目的网点编码
             ,dest_center_name --目的网点名称
             ,row_number() over(partition by code,center_code order by create_time desc) as row_id
        from jms_ods.yl_lmdm_sys_first_code
        where dt='{{ execution_date | cst_ds }}' and
                is_enable =1 and is_delete=1
    ) tmp where row_id = 1
),
---二段码
second_code as (
    select *
    from (
        select
            code
             ,network_id
             ,network_name
             ,network_code
             ,country_id
             ,province_id
             ,city_id
             ,area_id
             ,town_id
             ,center_id
             ,center_name
             ,center_code
             ,center_province_id
             ,center_city_id
             ,center_area_id
             ,row_number() over(partition by code,center_code order by create_time desc) as row_id
        from jms_ods.yl_lmdm_sys_second_code
        where dt='{{ execution_date | cst_ds }}'
          and is_enable =1 and is_delete=1
     ) tmp where row_id = 1
),
---三段码
     third_code as
         (
            select
                 code,
                 network_id,
                 network_name,
                 network_code,
                 deliver_id,
                 deliver_name,
                 deliver_code
             from (select
                          a.code as code,
                          a.network_id as network_id,
                          c.name as network_name,
                          a.network_code as network_code,
                          b.staff_id as deliver_id,
                          b.staff_name as deliver_name,
                          b.staff_code as deliver_code
                          from
                          (select
                           code,
                           network_id,
                           network_code,
                           id
                          from jms_ods.yl_lmdm_sys_tail_code
                          where dt='{{ execution_date | cst_ds }}'
                          and is_enable =1 and is_delete=1
                          ) as a
                          join
                          (select code,name
                   	    from  jms_dim.dim_network_whole_massage) c
                          on a.network_code= c.code
                          left join
                          (select
                          tail_code,
                          staff_id,
                          staff_name,
                          staff_code,
                          tail_id
                          from jms_ods.yl_lmdm_sys_tail_code_staff
                          where dt='{{ execution_date | cst_ds }}') as b
                          on a.code = b.tail_code and a.id =b.tail_id
                          ) d
             group by
                 code,network_id,network_name,network_code,deliver_id,deliver_name,deliver_code
         ),
     area as
         (
             select id,cn_name
             from jms_ods.yl_lmdm_sys_area
             where dt='{{ execution_date | cst_ds }}'
         ),

     code_all as (
         select

             first_code.code as first_code   ---集包编码(一段码)
              ,first_code.name  as first_name               ---集包名称(一段码)
              ,first_code.attributes  as first_code_attributes          ---集包属性 直 拆
              ,first_code.center_id            ---转运中心id
              ,first_code.center_name          ---转运中心名称
              ,first_code.center_code          ---转运中心编码
              ,second_code.center_province_id  ---转运中心所在省
              ,area1.cn_name   as center_province  ---转运中心所在省
              ,second_code.center_city_id      ---转运中心所在市
              ,area2.cn_name   as center_city  ---转运中心所在省
              ,second_code.center_area_id      ---转运中心所在区
              ,area3.cn_name   as center_area
              ,second_code.code as second_code ---二段码
              ,second_code.network_id          ---派件网点id
              ,second_code.network_name        ---派件网点名称
              ,second_code.network_code        ---派件网点编码
              ,second_code.country_id  as network_country_id          --派件网点所在国家
              ,area4.cn_name   as network_country
              ,second_code.province_id as network_province_id         --派件网点所在省
              ,area5.cn_name   as network_province        --派件网点所在省
              ,second_code.city_id     as network_city_id             --派件网点所在市
              ,area6.cn_name   as network_city             --派件网点所在市
              ,second_code.area_id     as network_area_id             --派件网点所在区
              ,area7.cn_name   as network_area            --派件网点所在区
              ,second_code.town_id     as network_town_id             --派件网点所在乡镇
              ,area8.cn_name   as network_town            --派件网点所在乡镇
              ,third_code.code as third_code
              ,third_code.deliver_id           ---派件员
              ,third_code.deliver_name         ---派件员名称
              ,third_code.deliver_code         ---派件员编码
              ,first_code.dest_center_code     ---目的中心编码
              ,first_code.dest_center_name     ---目的中心名称
         from  first_code
                   left join second_code
                             on first_code.center_code=second_code.center_code
                   full join third_code
                             on third_code.network_code=second_code.network_code
                   left join area area1 on second_code.center_province_id=area1.id
                   left join area area2 on second_code.center_city_id=area2.id
                   left join area area3 on second_code.center_area_id=area3.id
                   left join area area4 on second_code.country_id=area4.id
                   left join area area5 on second_code.province_id=area5.id
                   left join area area6 on second_code.city_id=area6.id
                   left join area area7 on second_code.area_id=area7.id
                   left join area area8 on second_code.town_id=area8.id
     )

insert overwrite table bidefault.dim_first_second_third_code_info_dt partition(dt)
select   1 as code_type
     ,first_code               ---集包编码(一段码)
     ,max(first_name)   as  first_name          ---集包名称(一段码)
     ,max(first_code_attributes) as first_code_attributes    ---集包属性 直 拆
     ,max(center_id)   as center_id         ---转运中心id
     ,max(center_name) as center_name        ---转运中心名称
     ,center_code      as center_code       ---转运中心编码
     ,max(center_province_id)  as  center_province_id---转运中心所在省
     ,max(center_province)  as center_province   ---转运中心所在省
     ,max(center_city_id)  as  center_city_id   ---转运中心所在市
     ,max(center_city)  as   center_city     ---转运中心所在省
     ,max(center_area_id)  as  center_area_id   ---转运中心所在区
     ,max(center_area)   as   center_area    ---二段码
     ,null as second_code
     ,null as network_id          ---派件网点id
     ,null as network_name        ---派件网点名称
     ,null as network_code        ---派件网点编码
     ,null as network_country_id          --派件网点所在国家
     ,null as network_country
     ,null as network_province_id         --派件网点所在省
     ,null as network_province        --派件网点所在省
     ,null as network_city_id             --派件网点所在市
     ,null as network_city             --派件网点所在市
     ,null as network_area_id             --派件网点所在区
     ,null as network_area            --派件网点所在区
     ,null as network_town_id             --派件网点所在乡镇
     ,null as network_town            --派件网点所在乡镇
     ,null as thrid_code
     ,null as deliver_id           ---派件员
     ,null as deliver_name         ---派件员名称
     ,null as deliver_code         ---派件员编码
     ,max(dest_center_code) as dest_center_code
     ,max(dest_center_name) as dest_center_name
     ,'{{ execution_date | cst_ds }}' as dt
from
    code_all
group by first_code,center_code

union all
--一段码、二段码关联
select   2 as code_type
     ,first_code               ---集包编码(一段码)
     ,max(first_name)   as  first_name          ---集包名称(一段码)
     ,max(first_code_attributes) as first_code_attributes    ---集包属性 直 拆
     ,max(center_id)   as center_id         ---转运中心id
     ,max(center_name) as center_name        ---转运中心名称
     ,center_code      as center_code       ---转运中心编码
     ,max(center_province_id)  as  center_province_id---转运中心所在省
     ,max(center_province)  as center_province   ---转运中心所在省
     ,max(center_city_id)  as  center_city_id   ---转运中心所在市
     ,max(center_city)  as   center_city     ---转运中心所在省
     ,max(center_area_id)  as  center_area_id   ---转运中心所在区
     ,max(center_area)   as   center_area    ---二段码
     ,second_code
     ,max(network_id) as network_id          ---派件网点id
     ,max(network_name) as network_name        ---派件网点名称
     ,network_code        ---派件网点编码
     ,max(network_country_id)  as network_country_id          --派件网点所在国家
     ,max(network_country) as network_country
     ,max(network_province_id)  as network_province_id         --派件网点所在省
     ,max(network_province) as network_province        --派件网点所在省
     ,max(network_city_id) as network_city_id             --派件网点所在市
     ,max(network_city) as network_city             --派件网点所在市
     ,max(network_area_id)  as network_area_id             --派件网点所在区
     ,max(network_area) as network_area            --派件网点所在区
     ,max(network_town_id) as network_town_id             --派件网点所在乡镇
     ,max(network_town) as network_town            --派件网点所在乡镇
     ,null as thrid_code
     ,null as deliver_id           ---派件员
     ,null as deliver_name         ---派件员名称
     ,null as deliver_code         ---派件员编码
     ,max(dest_center_code) as dest_center_code
     ,max(dest_center_name) as dest_center_name
     ,'{{ execution_date | cst_ds }}' as dt
from
    code_all
where second_code is not null
group by first_code,center_code,second_code,network_code

union all
--三段码（第一段、第二段、第三段）
select  3 as code_type, code_all.*,'{{ execution_date | cst_ds }}' as dt
from  code_all
where code_all.third_code is not null;



